package hk.ust.cse.comp3111.studentSchedule;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class CourseDatabaseHandler extends SQLiteOpenHelper{
	
	// All Static variables
    // Database Version
    private static final int DATABASE_VERSION = 1;
 
    // Database Name
    private static final String DATABASE_NAME = "course";
 
    // Contacts table name
    private static final String TABLE_COURSEINFO = "courseInfo";
 
    // Contacts Table Columns names
    private static final String KEY_CLASSNO = "classNo";
    private static final String KEY_COURSECODE = "courseCode";
    private static final String KEY_SECTION = "section";
    private static final String KEY_COURSENAME = "courseName";
    private static final String KEY_FROMDATE = "fromDate";
    private static final String KEY_VENUE = "venue";
    private static final String KEY_REP1_FROMTIME = "rep1_fromTime";
    private static final String KEY_REP1_TOTIME = "rep1_toTime";
    private static final String KEY_REP2_FROMTIME = "rep2_fromTime";
    private static final String KEY_REP2_TOTIME = "rep2_toTime";
    private static final String KEY_REP3_FROMTIME = "rep3_fromTime";
    private static final String KEY_REP3_TOTIME = "rep3_toTime";
    private static final String KEY_REP1_WEEKDAY = "rep1_weekDay";
    private static final String KEY_REP2_WEEKDAY = "rep2_weekDay";    
    private static final String KEY_REP3_WEEKDAY = "rep3_weekDay";
    private static final String KEY_STRUCTURE = "structure";
    private static final String KEY_TYPE = "type";
    
    
    public CourseDatabaseHandler(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
 
    // Creating Tables
    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_COURSEINFO + "("
                + KEY_CLASSNO + " INTEGER PRIMARY KEY," 
        		+ KEY_COURSECODE + " TEXT,"
                + KEY_SECTION + " TEXT," 
                + KEY_COURSENAME + " TEXT," 
                + KEY_FROMDATE + " TEXT," 
                + KEY_VENUE + " TEXT," 
                + KEY_REP1_FROMTIME + " INTEGER," 
                + KEY_REP1_TOTIME + " INTEGER," 
                + KEY_REP2_FROMTIME + " INTEGER," 
                + KEY_REP2_TOTIME + " INTEGER," 
                + KEY_REP3_FROMTIME + " INTEGER," 
                + KEY_REP3_TOTIME + " INTEGER," 
                + KEY_REP1_WEEKDAY + " INTEGER," 
                + KEY_REP2_WEEKDAY + " INTEGER," 
                + KEY_REP3_WEEKDAY + " INTEGER," 
                + KEY_STRUCTURE + " INTEGER," 
                + KEY_TYPE + " INTEGER"                 
        		+ ")";
        db.execSQL(CREATE_CONTACTS_TABLE);
    }
 
    // Upgrading database
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_COURSEINFO);
        // Create tables again
        onCreate(db);
    }
    
    public void insertClass(int ClassNo, String courseCode, String section, String courseName, String fromDate, String venue, 
    		long rep1_fromTime, long rep1_toTime, long rep2_fromTime,
			long rep2_toTime, long rep3_fromTime, long rep3_toTime,
			long rep1_weekDay, long rep2_weekDay, long rep3_weekDay,
			int structure, int type){
    	SQLiteDatabase db = this.getWritableDatabase();
    	ContentValues values = new ContentValues();
    	values.put(KEY_CLASSNO, ClassNo);
    	values.put(KEY_COURSECODE, courseCode);
    	values.put(KEY_SECTION, section);
    	values.put(KEY_COURSENAME, courseName);
    	values.put(KEY_FROMDATE, fromDate);
    	values.put(KEY_VENUE, venue);
    	values.put(KEY_REP1_FROMTIME, rep1_fromTime);
    	values.put(KEY_REP1_TOTIME, rep1_toTime);
    	values.put(KEY_REP2_FROMTIME, rep2_fromTime);
    	values.put(KEY_REP2_TOTIME, rep2_toTime);
    	values.put(KEY_REP3_FROMTIME, rep3_fromTime);
    	values.put(KEY_REP3_TOTIME, rep3_toTime);
    	values.put(KEY_REP1_WEEKDAY, rep1_weekDay);
    	values.put(KEY_REP2_WEEKDAY, rep2_weekDay);
    	values.put(KEY_REP3_WEEKDAY, rep3_weekDay);
    	values.put(KEY_STRUCTURE, structure);
    	values.put(KEY_TYPE, type);
    	
    	db.insert(TABLE_COURSEINFO, null, values);
    	db.close();
    }
    
    public Course getCourseByClassNo(int classNo){
    	SQLiteDatabase db = this.getReadableDatabase();
    	String selectQuery = "SELECT  * FROM " + TABLE_COURSEINFO + " WHERE "
                + KEY_CLASSNO + " = " + classNo;
    	Cursor cursor = db.rawQuery(selectQuery, null);
    	
    	if (cursor != null)
    		cursor.moveToFirst();
    	
    	Course courseResult = new Course();
    	courseResult.setClassNo(cursor.getInt(cursor.getColumnIndex(KEY_CLASSNO)));
    	courseResult.setCourseCode(cursor.getString(cursor.getColumnIndex(KEY_COURSECODE)));
    	courseResult.setSection(cursor.getString(cursor.getColumnIndex(KEY_SECTION)));
    	courseResult.setCourseName(cursor.getString(cursor.getColumnIndex(KEY_COURSENAME)));
    	courseResult.setFromDate(cursor.getString(cursor.getColumnIndex(KEY_FROMDATE)));
    	courseResult.setVenue(cursor.getString(cursor.getColumnIndex(KEY_VENUE)));
    	courseResult.setRep1_fromTime(cursor.getInt(cursor.getColumnIndex(KEY_REP1_FROMTIME)));
    	courseResult.setRep1_toTime(cursor.getInt(cursor.getColumnIndex(KEY_REP1_TOTIME)));
    	courseResult.setRep2_fromTime(cursor.getInt(cursor.getColumnIndex(KEY_REP2_FROMTIME)));
    	courseResult.setRep2_toTime(cursor.getInt(cursor.getColumnIndex(KEY_REP2_TOTIME)));    	
    	courseResult.setRep3_fromTime(cursor.getInt(cursor.getColumnIndex(KEY_REP3_FROMTIME)));
    	courseResult.setRep3_toTime(cursor.getInt(cursor.getColumnIndex(KEY_REP3_TOTIME)));
    	courseResult.setRep1_weekDay(cursor.getInt(cursor.getColumnIndex(KEY_REP1_WEEKDAY)));
    	courseResult.setRep2_weekDay(cursor.getInt(cursor.getColumnIndex(KEY_REP2_WEEKDAY)));
    	courseResult.setRep3_weekDay(cursor.getInt(cursor.getColumnIndex(KEY_REP3_WEEKDAY)));
    	courseResult.setStructure(cursor.getInt(cursor.getColumnIndex(KEY_STRUCTURE)));
    	courseResult.setType(cursor.getInt(cursor.getColumnIndex(KEY_TYPE)));
    	
    	return courseResult;
    }
    
    //used for autocompleted list
    public List<String> getAllCourseCode(){
    	List<String> list = new ArrayList<String>();
    	String selectQuery = "SELECT DISTINCT courseCode FROM " + TABLE_COURSEINFO;
    	
    	SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        
        if (cursor.moveToFirst()) {
            do {
            	list.add(cursor.getString(cursor.getColumnIndex(KEY_COURSECODE)));
            } while (cursor.moveToNext());
        }
        
        return list;

    }
    
    public List<Course> getCourseByCourseCode(String courseCode){
    	List<Course> list = new ArrayList<Course>();
    	
    	String selectQuery = "SELECT  * FROM " + TABLE_COURSEINFO + " WHERE courseCode = '"+courseCode+"'";
    	
    	//String selectQuery = "SELECT DISTINCT courseCode FROM " + TABLE_COURSEINFO;
    	
    	SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        
        if (cursor.moveToFirst()) {
            do {
            	Course courseResult = new Course();
            	courseResult.setClassNo(cursor.getInt(cursor.getColumnIndex(KEY_CLASSNO)));
            	courseResult.setCourseCode(cursor.getString(cursor.getColumnIndex(KEY_COURSECODE)));
            	courseResult.setSection(cursor.getString(cursor.getColumnIndex(KEY_SECTION)));
            	courseResult.setCourseName(cursor.getString(cursor.getColumnIndex(KEY_COURSENAME)));
            	courseResult.setFromDate(cursor.getString(cursor.getColumnIndex(KEY_FROMDATE)));
            	courseResult.setVenue(cursor.getString(cursor.getColumnIndex(KEY_VENUE)));
            	courseResult.setRep1_fromTime(cursor.getLong(cursor.getColumnIndex(KEY_REP1_FROMTIME)));
            	courseResult.setRep1_toTime(cursor.getLong(cursor.getColumnIndex(KEY_REP1_TOTIME)));
            	courseResult.setRep2_fromTime(cursor.getLong(cursor.getColumnIndex(KEY_REP2_FROMTIME)));
            	courseResult.setRep2_toTime(cursor.getLong(cursor.getColumnIndex(KEY_REP2_TOTIME)));    	
            	courseResult.setRep3_fromTime(cursor.getLong(cursor.getColumnIndex(KEY_REP3_FROMTIME)));
            	courseResult.setRep3_toTime(cursor.getLong(cursor.getColumnIndex(KEY_REP3_TOTIME)));
            	courseResult.setRep1_weekDay(cursor.getLong(cursor.getColumnIndex(KEY_REP1_WEEKDAY)));
            	courseResult.setRep2_weekDay(cursor.getLong(cursor.getColumnIndex(KEY_REP2_WEEKDAY)));
            	courseResult.setRep3_weekDay(cursor.getLong(cursor.getColumnIndex(KEY_REP3_WEEKDAY)));
            	courseResult.setStructure(cursor.getInt(cursor.getColumnIndex(KEY_STRUCTURE)));
            	courseResult.setType(cursor.getInt(cursor.getColumnIndex(KEY_TYPE)));
            	list.add(courseResult);
            } while (cursor.moveToNext());
        }
        
        return list;

    }
    
    
    public Course getCourseByCourseCodeAndSection(String courseCode, String section){
    	
    	String selectQuery = "SELECT  * FROM " + TABLE_COURSEINFO + " WHERE courseCode = '"+courseCode+"' AND section='" +section+"'";
    	SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        
        if (cursor != null)
    		cursor.moveToFirst();
    	
    	Course courseResult = new Course();
    	courseResult.setClassNo(cursor.getInt(cursor.getColumnIndex(KEY_CLASSNO)));
    	courseResult.setCourseCode(cursor.getString(cursor.getColumnIndex(KEY_COURSECODE)));
    	courseResult.setSection(cursor.getString(cursor.getColumnIndex(KEY_SECTION)));
    	courseResult.setCourseName(cursor.getString(cursor.getColumnIndex(KEY_COURSENAME)));
    	courseResult.setFromDate(cursor.getString(cursor.getColumnIndex(KEY_FROMDATE)));
    	courseResult.setVenue(cursor.getString(cursor.getColumnIndex(KEY_VENUE)));
    	courseResult.setRep1_fromTime(cursor.getInt(cursor.getColumnIndex(KEY_REP1_FROMTIME)));
    	courseResult.setRep1_toTime(cursor.getInt(cursor.getColumnIndex(KEY_REP1_TOTIME)));
    	courseResult.setRep2_fromTime(cursor.getInt(cursor.getColumnIndex(KEY_REP2_FROMTIME)));
    	courseResult.setRep2_toTime(cursor.getInt(cursor.getColumnIndex(KEY_REP2_TOTIME)));    	
    	courseResult.setRep3_fromTime(cursor.getInt(cursor.getColumnIndex(KEY_REP3_FROMTIME)));
    	courseResult.setRep3_toTime(cursor.getInt(cursor.getColumnIndex(KEY_REP3_TOTIME)));
    	courseResult.setRep1_weekDay(cursor.getInt(cursor.getColumnIndex(KEY_REP1_WEEKDAY)));
    	courseResult.setRep2_weekDay(cursor.getInt(cursor.getColumnIndex(KEY_REP2_WEEKDAY)));
    	courseResult.setRep3_weekDay(cursor.getInt(cursor.getColumnIndex(KEY_REP3_WEEKDAY)));
    	courseResult.setStructure(cursor.getInt(cursor.getColumnIndex(KEY_STRUCTURE)));
    	courseResult.setType(cursor.getInt(cursor.getColumnIndex(KEY_TYPE)));
    	
    	return courseResult;
        
    }
    
    public String getCourseName(String courseCode){
    	List<String> list = new ArrayList<String>();
    	String selectQuery = "SELECT  * FROM " + TABLE_COURSEINFO + " WHERE courseCode = '"+courseCode+"'";
    	
    	SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        
        if (cursor.moveToFirst()) {
            return cursor.getString(cursor.getColumnIndex(KEY_COURSENAME));
        }
        
        return null;

    }
    
    
	
	
	
}
